import pymysql
import pandas as pd
from sqlalchemy import create_engine


class MysqlConn:
    # def __init__(self, ):
    def __init__(self, host, user, password, db, port=3306, charset="utf8mb4"):
        self.engine = (f'mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset={charset}')
        self.conn = pymysql.connect(host=host, user=user, password=password, database=db, port=port, charset=charset)

    def xls_to_mysql(self, table_name, sheet_name, xls_path=None):
        """
        excel导入mysql
        :param table_name: mysql表名
        :param sheet_name: excel 表名
        :param xls_path:  excel文件路径
        :return:
        """

        df = pd.read_excel(xls_path, sheet_name=sheet_name)
        print(df)
        pd.io.sql.to_sql(df, table_name, con=self.engine, index=True, if_exists='replace', dtype=None)
        return True

    def read_mysql(self, table_name, sql=None, out=False):
        """
        读取mysql 的表
        :param table_name:
        :return:
        """
        sql = sql or f"SELECT * FROM {table_name}"
        df_chunk = pd.read_sql(sql=sql, con=self.engine, chunksize=1000)
        # print(df_chunk)
        df_data = pd.concat(df_chunk)
        if out:
            df_data.to_csv(f'mysql_{table_name}.csv', sep=',', header=True, index=False)
        return df_data


if __name__ == '__main__':
    # mysql_ingest = MysqlConn('127.0.0.1', 'root', '12345678', 'scu_committee')
    mysql_ingest = MysqlConn('211.83.159.144', 'root', 'S0beydbaAt2o2o', 'datashow')
    # mysql_ingest.to_mysql('tb_tp_laboratory', '科研平台tb_tp_laboratory', '学科统计-表模型设计.xls')
    # mysql_ingest.to_mysql('tb_tp_equipment', '科研实验仪器tb_tp_equipment', '学科统计-表模型设计.xls')
    mysql_ingest.xls_to_mysql('tb_tp_title', 'tb_tp_title人才称号', '学科统计-表模型设计.xls')

    # mysql_ingest.read_mysql('tb_record_schedule')






